package com.epam.edu.dao;

import com.epam.edu.entity.InputMark;
import com.epam.edu.entity.Specialty;
import com.epam.edu.entity.Subject;

import java.sql.*;
import java.util.LinkedList;
import java.util.List;

public class DAOInputMark {
    public static int add(Connection connection,InputMark inputMark) {
        int id = -1;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            int subjectID = inputMark.getSubject().getId();
            String command = String.format("INSERT INTO InputMarks (specId,historyKZ,language,mathematics,subjId,mark) " +
                    "VALUES(%d,%d,%d,%d,%d,%d)",
                    inputMark.getSpecialty().getId(), inputMark.getHistoryKZMark(), inputMark.getLanguageMark(),
                    inputMark.getMathematicsMark(), subjectID, inputMark.getMark());
            statement = connection.createStatement();
            statement.executeUpdate(command, Statement.RETURN_GENERATED_KEYS);
            resultSet = statement.getGeneratedKeys();
            resultSet.next();
            id = resultSet.getInt(1);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) statement.close();
                if (resultSet != null) resultSet.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        return id;
    }

    public static void update(Connection connection,InputMark inputMark) {
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            int subjectID = inputMark.getSubject().getId();
            String command = String.format("UPDATE InputMarks SET " +
                    "specId=%d,historyKZ=%d,language=%d,mathematics=%d,subjId=%d,mark=%d WHERE id=%d",
                    inputMark.getSpecialty().getId(), inputMark.getHistoryKZMark(), inputMark.getLanguageMark(),
                    inputMark.getMathematicsMark(), subjectID, inputMark.getMark(), inputMark.getId());
            statement = connection.createStatement();
            statement.executeUpdate(command);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) statement.close();
                if (resultSet != null) resultSet.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
    }

    public static void delete(Connection connection,InputMark inputMark) {
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            String command = String.format("UPDATE InputMarks SET isDelete='true' WHERE id=%s", inputMark.getId());
            statement = connection.createStatement();
            statement.executeUpdate(command);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) statement.close();
                if (resultSet != null) resultSet.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
    }

    public static void remove(Connection connection,Specialty specialty) {
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            String command = String.format("DELETE Specialties WHERE id=%s", specialty.getId());
            statement = connection.createStatement();
            statement.executeUpdate(command);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) statement.close();
                if (resultSet != null) resultSet.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
    }

    public static InputMark selectById(Connection connection,int id) {
        InputMark inputMark = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            String command = "SELECT id,specId,historyKZ,language,mathematics,subjId,mark FROM InputMarks WHERE id=? AND isDelete='false'";
            statement = connection.prepareStatement(command);
            statement.setInt(1, id);
            resultSet = statement.executeQuery();
            if (resultSet.next()) {
                Specialty specialty = DAOSpecialty.selectById(connection,resultSet.getInt(2));
                Subject subject = DAOSubject.selectById(connection,resultSet.getInt(6));
                inputMark = new InputMark
                        (resultSet.getInt(1), specialty, resultSet.getInt(3),
                                resultSet.getInt(4), resultSet.getInt(5), subject, resultSet.getInt(7));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) statement.close();
                if (resultSet != null) resultSet.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        return inputMark;
    }

    public static List<InputMark> selectAll(Connection connection) {
        List<InputMark> inputMarks = new LinkedList<InputMark>();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            String command = "SELECT id,specId,historyKZ,language,mathematics,subjId,mark FROM InputMarks WHERE isDelete='false'";
            statement = connection.prepareStatement(command);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                Specialty specialty = DAOSpecialty.selectById(connection,resultSet.getInt(2));
                Subject subject = DAOSubject.selectById(connection,resultSet.getInt(6));
                InputMark inputMark = new InputMark
                        (resultSet.getInt(1), specialty, resultSet.getInt(3),
                                resultSet.getInt(4), resultSet.getInt(5), subject, resultSet.getInt(7));
                inputMarks.add(inputMark);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) statement.close();
                if (resultSet != null) resultSet.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        return inputMarks;
    }

    public static List<InputMark> selectByMarks(Connection connection,InputMark inputMark) {
        List<InputMark> inputMarks = new LinkedList<InputMark>();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            int subjectID = inputMark.getSubject().getId();
            String command = String.format("SELECT id,specId,historyKZ,language,mathematics,subjId,mark " +
                    "FROM InputMarks " +
                    "WHERE isDelete='false'" +
                    " AND historyKZ<=%d" +
                    " AND language<=%d" +
                    " AND mathematics<=%d" +
                    " AND subjId=%d" +
                    " AND mark<=%d", inputMark.getHistoryKZMark(), inputMark.getLanguageMark(),
                    inputMark.getMathematicsMark(), subjectID, inputMark.getMark());
            statement = connection.prepareStatement(command);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                Specialty specialty = DAOSpecialty.selectById(connection,resultSet.getInt(2));
                Subject subject = DAOSubject.selectById(connection,resultSet.getInt(6));
                InputMark select = new InputMark
                        (resultSet.getInt(1), specialty, resultSet.getInt(3),
                                resultSet.getInt(4), resultSet.getInt(5), subject, resultSet.getInt(7));
                inputMarks.add(select);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) statement.close();
                if (resultSet != null) resultSet.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        return inputMarks;
    }
}
